package com.pointcircle.estate.server.framework.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.pointcircle.core.IRepository;
import com.pointcircle.core.entity.User;
import com.pointcircle.estate.server.framework.entity.CheckQualityBatch;
import com.pointcircle.estate.server.framework.entity.CheckQualityProblem;

@Repository
public interface CheckQualityProblemRepository extends IRepository<CheckQualityProblem, String> {
	
	@Query("select o from CheckQualityProblem o where o.checkQualityBatch = :checkQualityBatch and o.registBy = :user order by o.registDate desc")
	List<CheckQualityProblem> findByCheckQualityBatchAndRegistBy(
		@Param("checkQualityBatch") CheckQualityBatch checkQualityBatch,@Param("user") User user);
	
	List<CheckQualityProblem> findByCheckQualityBatchAndRepairBy(
		CheckQualityBatch checkQualityBatch, User user);
	
	List<CheckQualityProblem> findByRegistBy(User user);
	
	List<CheckQualityProblem> findByCheckQualityBatch(CheckQualityBatch batch);
	
	@Query("select o from CheckQualityProblem o where o.id in (:problemIds)")
	List<CheckQualityProblem> findProblemsByProblemIds(@Param("problemIds") List<String> problemIds);
	
	@Query(value = "SELECT * FROM tb_checkquality_problem t WHERE " + 
			"t.regist_date > str_to_date(?2, '%Y%m%d') " + 
			"and checkquality_problem_id in ( " + 
			"SELECT checkquality_problem_id FROM tb_checkquality_cc q WHERE q.cc = ?1 AND q.is_delete = 0)", nativeQuery = true)
	List<CheckQualityProblem> findByCcAndDate(String ccId, String date);
	
	@Query(value = "SELECT * FROM tb_checkquality_problem t WHERE " + 
			"t.regist_date > str_to_date(?2, '%Y%m%d') " + 
			"and t.regist_date < str_to_date(?3, '%Y%m%d') " + 
			"and checkquality_problem_id in ( " + 
			"SELECT checkquality_problem_id FROM tb_checkquality_cc q WHERE q.cc = ?1 AND q.is_delete = 0)", nativeQuery = true)
	List<CheckQualityProblem> findByCcAndTwoDate(String ccId, String startDate, String endDate);
	
	@Query(value = "SELECT count(*) FROM tb_checkquality_problem t WHERE " + 
			"t.regist_date > str_to_date(?2, '%Y%m%d') " + 
			"and t.regist_date < str_to_date(?3, '%Y%m%d') " + 
			"and checkquality_problem_id in ( " + 
			"SELECT checkquality_problem_id FROM tb_checkquality_cc q WHERE q.cc = ?1 AND q.is_delete = 0)", nativeQuery = true)
	int findCountByCcAndTwoDate(String ccId, String startDate, String endDate);
	
	@Query(value="select * from tb_checkquality_problem t where " + 
			"t.batch_id = ?1 and t.regist_by in ( ?2 ) order by t.regist_date desc LIMIT ?3, ?4", nativeQuery = true)
	List<CheckQualityProblem> findByCheckQualityBatchAndUserIdsAndLastItemIndex(String batchId, List<String> UserIds, int lastItemIndex, int itemCount);
	
	@Query(value="SELECT count(*) FROM tb_checkquality_problem t WHERE " +
			"t.batch_id = ?1 AND t.regist_by in (?2)", nativeQuery = true)
	int findByCheckQualityBatchAndUserIdsCount(String batchId, List<String> UserIds);
	
	CheckQualityProblem findByIndexTimeout(String indexTimeout);

	@Query(value="select * from tb_checkquality_problem t "
			+ "where t.regist_by = ?1 "
			+ "or checkquality_problem_id in( "
			+ "select checkquality_problem_id from tb_checkquality_repair "
			+ "where repair = ?1) "
			+ "or checkquality_problem_id in("
			+ "select checkquality_problem_id from tb_checkquality_reinspect "
			+ "where reinspect = ?1)", nativeQuery = true)
	List<CheckQualityProblem> findProblemByUser(String userId);
	
	@Query(value = "SELECT * FROM tb_checkquality_problem t WHERE " + 
			"checkquality_problem_id in ( " + 
			"SELECT checkquality_problem_id FROM tb_checkquality_cc q WHERE q.cc = ?1 AND q.is_delete = 0)", nativeQuery = true)
	List<CheckQualityProblem> findByCc(String ccId);
	
}
